with tmp_network as (
    select
        case when network_type = 3 then 1
             when network_type = 4 then 2
             when network_type = 5 then 3
             when network_type = 6 then 4
             when network_type = 2 then 5
             when network_type = 1 then 6
             else 0
        end as network_type
        ,code
        ,name as network_name
        ,agent_code
        ,agent_name
        ,fran_code as franchisee_code
        ,fran_name as franchisee_name
        ,center_code as transcenter_code
        ,center_name as transcenter_name
    from jms_dim.dim_network_whole_massage
)

insert overwrite table jms_dwd.dwd_tab_barscan_notallow_base_dt partition(dt)
select
    barscan.recordid as record_id
    ,barscan.billcode as bill_code
    ,barscan.listcode as list_code
    ,barscan.subbillcode as subbill_code
    ,barscan.transfercode as transfer_code
    ,barscan.packagecode as package_code
    ,barscan.scantype as scan_type
    ,barscan.nextstation as next_station
    ,barscan.destination as destination
    ,barscan.scanuser as scan_user
    ,barscan.inputsite as input_site
    ,barscan.scantime as scan_time
    ,barscan.inputtime as input_time
    ,barscan.operatedate as operate_date
    ,barscan.send_deliver_user as send_deliver_user
    ,barscan.sendcustomer as send_customer
    ,barscan.sendsite as send_site
    ,barscan.destsite as dest_site
    ,barscan.transfercenter as transfer_center
    ,barscan.pcs as pcs
    ,barscan.weight as weight
    ,barscan.goodstype as goods_type
    ,barscan.expresstype as transport_type
    ,barscan.shifts as shifts
    ,barscan.transfer_deliver_fee as transfer_deliver_fee
    ,barscan.networkfee as network_manage_fee
    ,barscan.otherfee as other_fee
    ,barscan.checkflag as check_flag
    ,barscan.accountflag as account_flag
    ,barscan.receiptflag as receipt_flag
    ,barscan.returnflag as return_flag
    ,barscan.elescaleflag as ele_scale_flag
    ,barscan.unrecordflag as unrecord_flag
    ,barscan.writebackflag as write_back_flag
    ,barscan.lockflag as lock_flag
    ,barscan.remark as remark
    ,barscan.modifyuser as modify_user
    ,barscan.modifytime as modify_time
    ,barscan.mobile as mobile
    ,barscan.pistolid as pistol_id
    ,barscan.remark1 as remark1
    ,barscan.remark2 as remark2
    ,barscan.scansitecode as scan_site_code
    ,barscan.uploadtime as upload_time
    ,barscan.remark3 as len_wid_hei_product
    ,barscan.scanusercode as scan_user_code
    ,barscan.send_deliver_usercode as send_deliver_user_code
    ,barscan.nextstationcode as next_station_code
    ,barscan.source as source
    ,barscan.remark5 as remark5
    ,barscan.remark6 as remark6
    ,barscan.remark4 as remark4
    ,barscan.scansiteid as scan_site_id
    ,barscan.nextstationid as next_station_id
    ,barscan.scanuserid as scan_user_id
    ,barscan.send_deliver_userid as send_deliver_user_id
    ,tmp_network.network_type as network_type
    ,tmp_network.agent_code as  site_agent_code
    ,tmp_network.agent_name  as site_agent_name
    ,tmp_network.franchisee_code as site_franchisee_code
    ,tmp_network.franchisee_name as site_franchisee_name
    ,tmp_network.transcenter_code as site_transcenter_code
    ,tmp_network.transcenter_name as site_transcenter_name
    ,tmp_network_pre.network_type as pre_network_type
    ,case
        when (length(billcode) = 13) then 'GB'
        when (length(billcode) = 15 and billcode like "55%") then 'BS'
        when (length(billcode) = 12 and (billcode like "40%" or billcode like "42%" )) then 'BSB'
        when (length(billcode) = 15 and billcode like "JT%") then 'JT'
        when (length(billcode) = 13 and billcode like "B%") then 'JTB'
        else "Others"
     end as waybill_source
    ,barscan.dt as dt
from jms_ods.tab_barscan_notallow barscan
left join tmp_network tmp_network on barscan.scansitecode = tmp_network.code
left join tmp_network tmp_network_pre on barscan.nextstationcode = tmp_network_pre.code
where barscan.dt = '{{ execution_date | cst_ds }}';
